package com.space.school.dao.impl;

import com.space.school.dao.InfoDao;
import com.space.school.domain.Info;
import com.space.school.domain.ProductCategory;
import com.space.school.domain.User;
import com.space.school.utils.SpaceJDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.*;

public class InfoDaoImpl implements InfoDao {
    //    获取数据库链接
    private JdbcTemplate jdbcTemplate=new JdbcTemplate(SpaceJDBCUtils.getDataSource());

    @Override
    public List<Info> search(Map<String, String[]> map, int currentPage, int pageSize) {
//        StringBuffer sql=new StringBuffer("select * from product_info where 99=99 ");
        StringBuffer sql=new StringBuffer("select  info.product_id, info.product_name, info.product_price, info.product_stock, info.product_description, info.product_icon, info.product_status, info.category_type, info.create_time, info.update_time, cate.category_name from product_info info LEFT JOIN product_category cate ON  cate.category_type= info.category_type where 1=1");
        //        取值的集合
        ArrayList<Object> params=new ArrayList<>();
//        keySet 取map中的建的数组
//        [{用户名={11}},{生效表示={生效}}]
        Set<String> set=map.keySet();
        for(String key:set){
            if(!map.get(key)[0].equals("") && !key.equals("currentPage")){
                sql.append(" and "+key+ " like ?" );
                params.add("%"+map.get(key)[0]+"%");
            }
        }
        params.add(currentPage);
        params.add(pageSize);
//        sql.append(" order by product_id limit ?,?");
        sql.append(" order by product_id limit ?,?");

        List<Info>A=jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<Info>(Info.class),params.toArray());
        return A;
    }

    @Override
    public int count(Map<String, String[]> map) {
        ArrayList<Object> params=new ArrayList<>();
        StringBuffer sql=new StringBuffer("select count(*) from product_info where 99=99");
        Set<String> set=map.keySet();
        for(String key:set){
            if(!map.get(key)[0].equals("") && !key.equals("currentPage")){
                sql.append(" and "+key+ " like ?" );
                params.add("%"+map.get(key)[0]+"%");
            }
        }
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class,params.toArray());
    }

    @Override
    public void del(int product_id) {
        String sql = "delete from product_info where product_id=?";
        jdbcTemplate.update(sql,product_id);
    }

    @Override
    public List<Info> find(int product_id) {
        String sql = "select * from product_info where product_id = ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Info>(Info.class),product_id);
    }

    @Override
    public void update(Info info) {
        String sql="UPDATE product_info SET product_price = ?,product_stock=? ,product_description=? ,product_status=?,category_type=? ,update_time=? WHERE product_id = ? ";
        jdbcTemplate.update(sql,info.getProduct_price(),info.getProduct_stock(),info.getProduct_description(),info.getProduct_status(),info.getCategory_type(),new Date(),info.getProduct_id());
    }

    @Override
    public void update2(Map<String, String[]> map) {
        StringBuffer sql=new StringBuffer();
        sql.append("update product_info set ");

        ArrayList<Object> params=new ArrayList<>();
//        把map中的建拿出来
        Set<String> set=map.keySet();
        int id=0;
        for(String key:set){
            if(!map.get(key)[0].equals("")){
                if(key.equals("product_id")){
                    id=Integer.parseInt(map.get(key)[0]);
                }else {
                    sql.append(" " + key + " = ? ,");
                    if(key.equals("product_price")||key.equals("product_stock")||key.equals("product_status")||key.equals("" +
                            "category_type")){
                        params.add(Integer.parseInt(map.get(key)[0]));
                    }else {
                        params.add(" "+map.get(key)[0]+" ");
                    }
                }
            }
        }
        sql.deleteCharAt(sql.length()-1);
        sql.append("where product_id=?");
        params.add(id);
        jdbcTemplate.update(sql.toString(),params.toArray());
    }

    @Override
    public int insert(Info info) {
//        判断名字是否重复 如果重复 返回1
        String sql_category_name="select product_name from product_info where product_name=?";
        List<Info> user_list= jdbcTemplate.query(sql_category_name,new BeanPropertyRowMapper<Info>(Info.class),info.getProduct_name());
        if(user_list.size()!=0){
            return 1;
        }
//      新增成功返回3
        String sql="insert into product_info (product_name,product_price,product_stock,product_description,product_status,category_type,create_time) values(?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,info.getProduct_name(),info.getProduct_price(),info.getProduct_stock(),info.getProduct_description(),info.getProduct_status(),info.getCategory_type(),new Date());
        return 3;

    }

    @Override
    public List<Info> findAll() {
        String sql = "select * from product_info";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Info>(Info.class));
    }

    @Override
    public int findStock(int product_id) {
        String sql="select product_stock from product_info where product_id=?";
        return jdbcTemplate.queryForObject(sql,Integer.class,product_id);
    }

    @Override
    public List<Info> findByUser(String order_id) {
        String sql="SELECT detail.order_id,sum(detail.product_num) product_sum,detail.product_id,info.product_name FROM order_detail detail LEFT JOIN product_info info ON detail.product_id=info.product_id  where order_id=?  GROUP BY detail.order_id,detail.product_id,info.product_name ";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Info>(Info.class),order_id);
    }

    @Override
    public List<Info> updateByUser(String order_id) {
        String sql="";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Info>(Info.class),order_id);

    }


}
